SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].gspProduct_SEARCH
(
@productId int = null,
@productCategoryId int = null,
@name nvarchar(128) = null,
@price money = null,
@quantity int = null,
@description nvarchar(4000) = null,
@createdDate datetime = null,
@lastModifiedDate datetime = null,
@isDeleted bit = null,
@isVisible bit = null
)
AS

SELECT
  *
FROM
  [dbo].[Product]
WHERE
  (@productId IS NULL OR [ProductId] = @productId)
AND
  (@productCategoryId IS NULL OR [ProductCategoryId] = @productCategoryId)
AND
  (@name IS NULL OR @name = '' OR [Name] LIKE @name + '%')
AND
  (@price IS NULL OR [Price] = @price)
AND
  (@quantity IS NULL OR [Quantity] = @quantity)
AND
  (@description IS NULL OR @description = '' OR [Description] LIKE @description + '%')
AND
  (@createdDate IS NULL OR [CreatedDate] = @createdDate)
AND
  (@lastModifiedDate IS NULL OR [LastModifiedDate] = @lastModifiedDate)
AND
  (@isDeleted IS NULL OR [IsDeleted] = @isDeleted)
AND
  (@isVisible IS NULL OR [IsVisible] = @isVisible)


GO
